package com.example.whatsforlunch;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class Description_Database extends SQLiteAssetHelper {

	private SQLiteDatabase db;
	
    private static final String DATABASE_NAME = "food_catalog";
    private static final int DATABASE_VERSION = 1;

  //column names, change to suit WHATS FOR LUNCH
    private final String TABLE_NAME = "description_table";
    private final String TABLE_ROW_ID = "id";
    private final String TABLE_ROW_ONE = "food_name";
    private final String TABLE_ROW_TWO = "description";
    private final String TABLE_ROW_THREE = "pantry";
    private final String TABLE_ROW_FOUR = "fridge";
    private final String TABLE_ROW_FIVE = "freezer";
    private final String TABLE_ROW_SIX = "category";
    
    public final String NOT_IN_SYSTEM = "NiS";
    
    public Description_Database(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION); 
        
        db = getReadableDatabase();
    }

    /**********************************************************************
     * RETRIEVING ALL ROWS FROM THE DATABASE TABLE
     *
     * This is an example of how to retrieve all data from a database
     * table using this class. edit this method to suit WHATS FOR LUNCH
     *
     * the key is automatically assigned by the database
     */
    public ArrayList<ArrayList<Object>> getAllRowsAsArrays()
    {
            // create an ArrayList that will hold all of the data collected from
            // the database.
            //CREATE ARRAYLIST of FOOD OBJECTS?
            ArrayList<ArrayList<Object>> dataArrays =
                    new ArrayList<ArrayList<Object>>();
     
            // this is a database call that creates a "cursor" object.
            // the cursor object store the information collected from the
            // database and is used to iterate through the data.
            Cursor cursor;
     
            try
            {
                    // ask the database object to create the cursor.
                    cursor = db.query(
                                    TABLE_NAME,
                                    new String[]{TABLE_ROW_ID, 
                                    			 TABLE_ROW_ONE, 
                                    			 TABLE_ROW_TWO, 
                                    			 TABLE_ROW_THREE,
                                    			 TABLE_ROW_FOUR,
                                    			 TABLE_ROW_FIVE,
                                    			 TABLE_ROW_SIX},
                                    null, null, null, null, null
                    );
                  
                    // move the cursor's pointer to position zero.
                    cursor.moveToFirst();
     
                    // if there is data after the current cursor position, add it
                    // to the ArrayList.
                    if (!cursor.isAfterLast())
                    {
                            do
                            {
                                    ArrayList<Object> dataList = new ArrayList<Object>();
     
                                    dataList.add(cursor.getLong(0));
                                    dataList.add(cursor.getString(1));
                                    dataList.add(cursor.getString(2));
                                    dataList.add(cursor.getString(3));
                                    dataList.add(cursor.getString(4));
                                    dataList.add(cursor.getString(5));
                                    dataList.add(cursor.getString(6));
                                    dataArrays.add(dataList);
                            }
                            // move the cursor's pointer up one position.
                            while (cursor.moveToNext());
                    }
            }
            catch (SQLException e)
            {
                    Log.e("DB Error", e.toString());
                    e.printStackTrace();
            }
     
            // return the ArrayList that holds the data collected from
            // the database.
            return dataArrays;
    }
    
    public ArrayList<ArrayList<Object>> getAllFoods_in_Category(String category)
    {
            // create an ArrayList that will hold all of the data collected from
            // the database.
            //CREATE ARRAYLIST of FOOD OBJECTS?
            ArrayList<ArrayList<Object>> dataArrays =
                    new ArrayList<ArrayList<Object>>();
     
            // this is a database call that creates a "cursor" object.
            // the cursor object store the information collected from the
            // database and is used to iterate through the data.
            Cursor cursor;
     
            try
            {
                    // ask the database object to create the cursor.
                    cursor = db.query(
                                    TABLE_NAME,
                                    new String[]{TABLE_ROW_ID, 
                                    			 TABLE_ROW_ONE, 
                                    			 TABLE_ROW_TWO, 
                                    			 TABLE_ROW_THREE,
                                    			 TABLE_ROW_FOUR,
                                    			 TABLE_ROW_FIVE,
                                    			 TABLE_ROW_SIX},
                                    			 TABLE_ROW_SIX+ " =?", new String[] {category}, 
                                    null, null, null, null
                    );
                  
                    // move the cursor's pointer to position zero.
                    cursor.moveToFirst();
     
                    // if there is data after the current cursor position, add it
                    // to the ArrayList.
                    if (!cursor.isAfterLast())
                    {
                            do
                            {
                                    ArrayList<Object> dataList = new ArrayList<Object>();
     
                                    dataList.add(cursor.getLong(0));
                                    dataList.add(cursor.getString(1));
                                    dataList.add(cursor.getString(2));
                                    dataList.add(cursor.getString(3));
                                    dataList.add(cursor.getString(4));
                                    dataList.add(cursor.getString(5));
                                    dataList.add(cursor.getString(6));
                                    dataArrays.add(dataList);
                            }
                            // move the cursor's pointer up one position.
                            while (cursor.moveToNext());
                    }
            }
            catch (SQLException e)
            {
                    Log.e("DB Error", e.toString());
                    e.printStackTrace();
            }
     
            // return the ArrayList that holds the data collected from
            // the database.
            return dataArrays;
    }
    /**********************************************************************
     * RETRIEVING A ROW FROM THE DATABASE TABLE
     *
     * This is an example of how to retrieve a row from a database table
     * using this class. edit this method to suit WHATS FOR LUNCH.
     *
     * @param rowID the id of the row to retrieve
     * @return an array containing the data from the row
     */
    public ArrayList<Object> getRowAsArray_FoodName(String name)
    {
            // create an array list to store data from the database row.
           
           
            //CREATE ARRAYLIST 
            ArrayList<Object> rowArray = new ArrayList<Object>();
            Cursor cursor;
    		
            try
            {
                    // this is a database call that creates a "cursor" object.
                    // the cursor object store the information collected from the
                    // database and is used to iterate through the data.
                    cursor = 
                    		db.query
                    (
                                    TABLE_NAME,
                                    new String[]{TABLE_ROW_ID, 
                                    			 TABLE_ROW_ONE, 
                                    			 TABLE_ROW_TWO, 
                                    			 TABLE_ROW_THREE,
                                    			 TABLE_ROW_FOUR,
                                    			 TABLE_ROW_FIVE,
                                    			 TABLE_ROW_SIX},     
                                    TABLE_ROW_ONE+ " =?", new String[] {name},
                                    null, null, null, null
                    );
     
                    // move the pointer to position zero in the cursor.
                    cursor.moveToFirst();
     
                    // if there is data available after the cursor's pointer, add
                    // it to the ArrayList that will be returned by the method.
                    if (!cursor.isAfterLast())
                    {
                            do
                            {
                                    rowArray.add(cursor.getLong(0));
                                    rowArray.add(cursor.getString(1));
                                    rowArray.add(cursor.getString(2));
                                    rowArray.add(cursor.getString(3));
                                    rowArray.add(cursor.getString(4));
                                    rowArray.add(cursor.getString(5));
                                    rowArray.add(cursor.getString(6));
                            }
                            while (cursor.moveToNext());
                    }
     
                    // let java know done with with the cursor.
                    cursor.close();
            }
            catch (SQLException e)
            {
                    Log.e("DB ERROR", e.toString());
                    e.printStackTrace();
            }
     
            // return the ArrayList containing the given row from the database.
            return rowArray;
    }
    
    /************************************************************************
     * GET ALL UNIQUE CATEGORIES IN THE DATABASE
     * 
     * Retrieves all categories in the database by checking each item's
     * category value and adding it to a list if not already added. 
     * 
     * @return A list of categories found in the database
     * 
     */
    public ArrayList<String> getCategories()
    {
            //CREATE ARRAYLIST of categories
    		ArrayList<String> cats = new ArrayList<String>();
            Cursor cursor;
     
            try
            {
                    // this is a database call that creates a "cursor" object.
                    // the cursor object store the information collected from the
                    // database and is used to iterate through the data.
                    cursor = db.query
                    (
                    		TABLE_NAME,
                            new String[]{TABLE_ROW_SIX},
                            null, null, null, null, null
                    );
     
                    // move the pointer to position zero in the cursor.
                    cursor.moveToFirst();
     
                    // if there is data available after the cursor's pointer, add
                    // it to the ArrayList that will be returned by the method.
                    if (!cursor.isAfterLast())
                    {
                            do
                            {
                            		if( !cats.contains(cursor.getString(0)) ){
                            			cats.add(cursor.getString(0));
                            		}
                            }
                            while (cursor.moveToNext());
                    }
     
                    // let java know that you are through with the cursor.
                    cursor.close();
            }
            catch (SQLException e)
            {
                    Log.e("DB ERROR", e.toString());
                    e.printStackTrace();
            }
     
            // return the ArrayList containing the given row from the database.
            return cats;
    }
    /******************************************************************************
     * GET ALL FOOD IN A CATEGORY
     * 
     * Retrieves all the food under a given category and returns them as
     * an array list.
     * 
     * @param String of the wanted category
     * @return ArrayList of food from the given category
     * 
     */
    public ArrayList<String> getFoodsInCategory(String category)
    {
            // create an array list to store data from the database row.
            
           
            //CREATE ARRAYLIST 
    		ArrayList<String> cats = new ArrayList<String>();
            Cursor cursor;
     
            try
            {
                    // this is a database call that creates a "cursor" object.
                    // the cursor object store the information collected from the
                    // database and is used to iterate through the data.
                    cursor = db.query
                    (
                                    TABLE_NAME,
                                    new String[]{TABLE_ROW_ONE},     
                                     TABLE_ROW_SIX + "=?",
                                    new String[] {category}, null, null, null, null
                    );
     
                    // move the pointer to position zero in the cursor.
                    cursor.moveToFirst();
     
                    // if there is data available after the cursor's pointer, add
                    // it to the ArrayList that will be returned by the method.
                    if (!cursor.isAfterLast())
                    {
                            do
                            {
                            	cats.add(cursor.getString(0));
                            }
                            while (cursor.moveToNext());
                    }
     
                    // let java know that you are through with the cursor.
                    cursor.close();
            }
            catch (SQLException e)
            {
                    Log.e("DB ERROR", e.toString());
                    e.printStackTrace();
            }
     
            // return the ArrayList containing the given row from the database.
            return cats;
    }
    /**********************************************************************
     * FINDING SOONEST EXPIRATION DATE
     *
     * Looks for the soonest expiration date of the given item. Use
     * when looking for the worst case scenario expiration time for 
     * an item. 
     *
     * @param item name needing an expiration date
     * @return formatted string of the soonest expiration date
     */
    public String findSoonestExpiration(String item)
    {
            // create an array list to store data from the database row.
           
           
            //CREATE ARRAYLIST 
            ArrayList<Object> rowArray = new ArrayList<Object>();
            Cursor cursor;
    		
            try
            {
                    // this is a database call that creates a "cursor" object.
                    // the cursor object store the information collected from the
                    // database and is used to iterate through the data.
                    cursor = 
                    		db.query
                    (
                                    TABLE_NAME,
                                    new String[]{TABLE_ROW_THREE,
                                    			 TABLE_ROW_FOUR,
                                    			 TABLE_ROW_FIVE},     
                                    TABLE_ROW_ONE+ " =?", new String[] {item},
                                    null, null, null, null
                    );
     
                    // move the pointer to position zero in the cursor.
                    cursor.moveToFirst();
     
                    // if there is data available after the cursor's pointer, add
                    // it to the ArrayList that will be returned by the method.
                    if (!cursor.isAfterLast())
                    {
                            do
                            {
                                    rowArray.add(cursor.getInt(0));
                                    rowArray.add(cursor.getInt(1));
                                    rowArray.add(cursor.getInt(2));
                            }
                            while (cursor.moveToNext());
                    }
     
                    // let java know done with with the cursor.
                    cursor.close();
            }
            catch (SQLException e)
            {
                    Log.e("DB ERROR", e.toString());
                    e.printStackTrace();
            }
            //Find soonest expiration date
            int minTime = Integer.MAX_VALUE;
            for(Object i : rowArray){
            	if( (Integer)i != 0 && (Integer)i < minTime){
            		minTime = (Integer)i;
            	}
            }
            //If item was not in the system
            if(minTime == Integer.MAX_VALUE){
            	return NOT_IN_SYSTEM;//Not in System
            }
            
            // calculate and return the expiration date
            return calculateDate(minTime);
    }
    /**
     * CALCULATES FUTURE DATE
     * 
     * Gets a date the specified days in the future
     * @param days
     * @return
     */
    private String calculateDate(Integer days){
		Calendar c = Calendar.getInstance();
		c.add(Calendar.DATE, days);
		SimpleDateFormat df = new SimpleDateFormat("M/d/yyyy");
		
		return df.format(c.getTime());
	}
    
 }